﻿using picFilter.App_Code;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using System.Data.OracleClient;
using System.Text;
using System.Web;

namespace picFilter.Controllers
{
    public class picTypeRemarkGroupsController : ApiController
    {
        public class PicTypeRemarkGroup
        {
            public string userId { get; set; }
            public string typeName { get; set; }
            public string remark { get; set; }
            public PicGroup[] picGroup { get; set; }

        }

        public class PicGroup
        {
            public string groupName { get; set; }
            public Pic[] pic { get; set; }
        }

        public class Pic
        {
            public string picTypeRemarkGroupId { get; set; }
            public string id { get; set; }
            public string fileName { get; set; }
            public bool del { get; set; }
        }

        // GET api/pictyperemarkgroups
        /// <summary>
        /// 获取相片评语分组关系
        /// </summary>
        /// <returns></returns>
        public object Get()
        {
            //根据证件类型ID和评语ID获取
            try
            {
                int remarkId, typeId;
                int.TryParse(HttpContext.Current.Request.QueryString["remarkid"], out remarkId);
                int.TryParse(HttpContext.Current.Request.QueryString["typeid"], out typeId);
                string sqlCmdText = "SELECT pictyperemarkgroup.*,remarkgroup.groupName," +
                    "remark.remark,type.typeName,pic.fileName," +
                    "pic.fileFullName,pic.origName From pictyperemarkgroup " +
                    " Left Join remarkgroup On remarkgroup.id=pictyperemarkgroup.RemarkGroupId " +
                    " Left Join remark On remark.id=pictyperemarkgroup.RemarkId " +
                    " Left Join type On type.id=pictyperemarkgroup.TypeId " +
                    " Left Join pic On pic.id=pictyperemarkgroup.PicId Where " +
                    " pictyperemarkgroup.TypeId= " + typeId +
                    " and pictyperemarkgroup.RemarkId=" + remarkId;
                DataTable dt = DBHelper.ExecuteDataTable(sqlCmdText);

                return dt;
            }
            catch (Exception ex)
            {
                return new { error = "获取资源失败！", syserror = ex.Message };
            }
        }

        // GET api/pictyperemarkgroups/5
        public string Get(int id)
        {
            return "value";
        }

        // POST api/pictyperemarkgroups
        /// <summary>
        /// 提交相片证件类型、评语、分组（新增、编辑）记录
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public object Post([FromBody]PicTypeRemarkGroup obj)
        {
            try
            {
                //var content = req.Content.ReadAsStringAsync().Result;
                PicTypeRemarkGroup picTypeRemarkGroups = obj;
                bool hasValidGroup = false; //用于判断是否存在有效分组(至少有一个分组存在有一张相片)
                for (int i = 0; i < picTypeRemarkGroups.picGroup.Length; i++)
                {
                    if (picTypeRemarkGroups.picGroup[i].pic.Length > 1)
                    {
                        hasValidGroup = true;
                        break;
                    }
                }
                if (hasValidGroup)
                {
                    StringBuilder sbCmdText = new StringBuilder();
                    sbCmdText.Append("Declare Begin ");

                    //存在有效分组，将数据保存至数据库

                    //创建动态查询参数列表
                    List<OracleParameter> parameterList = new List<OracleParameter>();

                    parameterList.Add(DBHelper.createParameter("typeName", OracleType.VarChar, picTypeRemarkGroups.typeName));
                    parameterList.Add(DBHelper.createParameter("remark", OracleType.VarChar, picTypeRemarkGroups.remark));
                    parameterList.Add(DBHelper.createParameter("userId", OracleType.VarChar, picTypeRemarkGroups.userId));

                    //写入证件类型表
                    sbCmdText.Append("Insert Into type(id,typeName,userId) ");
                    sbCmdText.Append("Select type_seq.nextval,:typeName,:userId From dual ");
                    sbCmdText.Append("Where not Exists(Select 1 From type Where typeName=:typeName); ");

                    //写入评语表
                    sbCmdText.Append("Insert Into remark(id,remark,userId) ");
                    sbCmdText.Append("Select type_seq.nextval,:remark,:userId From dual ");
                    sbCmdText.Append("Where not Exists(Select 1 From remark Where remark=:remark); ");

                    //写入证件类型评语关系表
                    sbCmdText.Append("Insert Into typeRemark(id,typeId,remarkId,userId) ");
                    sbCmdText.Append("Select typeRemark_seq.nextval,type.ID,remark.ID,:userId From type,remark ");
                    sbCmdText.Append("Where type.typeName=:typeName and remark.remark=:remark ");
                    sbCmdText.Append("and not Exists(Select 1 From typeRemark Where typeId=type.ID and remarkid=remark.ID); ");

                    for (int i = 0; i < picTypeRemarkGroups.picGroup.Length; i++)
                    {
                        PicGroup tempGroup = picTypeRemarkGroups.picGroup[i];
                        
                        if (picTypeRemarkGroups.picGroup[i].pic.Length > 1)
                        {
                            string groupNameParam="groupName" + i;
                            parameterList.Add(DBHelper.createParameter(groupNameParam, OracleType.VarChar, tempGroup.groupName));
                            //写入分组表
                            sbCmdText.Append("Insert Into remarkGroup(id,remarkId,userId,groupName) ");
                            sbCmdText.Append("Select remarkGroup_seq.nextval,remark.ID,:userId,:" + groupNameParam);
                            sbCmdText.Append(" From remark Where remark=:remark ");
                            sbCmdText.Append("and not Exists (Select 1 From remarkGroup ");
                            sbCmdText.Append("Where remarkid=remark.ID and groupName=:" + groupNameParam + ");");

                            for (int j = 0; j < tempGroup.pic.Length - 1; j++)
                            {
                                string picIdParam = "picId" + i.ToString() + j.ToString();

                                parameterList.Add(DBHelper.createParameter(picIdParam, OracleType.Int32, tempGroup.pic[j].id));
                                //如果要删除图片
                                if (tempGroup.pic[j].del)
                                {
                                    sbCmdText.Append("Delete pic Where id=:" + picIdParam + ";");
                                    sbCmdText.Append("Delete picTypeRemarkGroup Where picId=:" + picIdParam + ";");
                                }

                                //如果已存在图片证件类型评语分组关系记录,更新
                                else if (!string.IsNullOrEmpty(tempGroup.pic[j].picTypeRemarkGroupId))
                                {
                                    string idParam = "picTypeRemarkGroupId" + i.ToString() + j.ToString();
                                    parameterList.Add(DBHelper.createParameter(idParam, OracleType.Int32, tempGroup.pic[j].picTypeRemarkGroupId));
                                    sbCmdText.Append("Update picTypeRemarkGroup Set ");
                                    sbCmdText.Append("typeId=(Select id From type Where typeName=:typeName and rownum=1),");
                                    sbCmdText.Append("remarkId=(Select id From remark Where remark.remark=:remark and rownum=1),");
                                    sbCmdText.Append("remarkGroupId=(Select remarkGroup.id From remarkGroup,remark Where remark.remark=:remark and remarkGroup.groupName=:" + groupNameParam + " and rownum=1),");
                                    sbCmdText.Append("userId=:userId ");
                                    sbCmdText.Append(" Where picTypeRemarkGroup.id=:" + idParam + ";");
                                }
                                else
                                {
                                    //写入图片证件类型评语分组关系表
                                    sbCmdText.Append("Insert Into picTypeRemarkGroup(id,picId,typeId,remarkId,remarkGroupId,userId) ");
                                    sbCmdText.Append("Select picTypeRemarkGroup_seq.nextval,pic.ID,type.ID,remark.ID,remarkGroup.ID,:userId ");
                                    sbCmdText.Append("From pic,type,remark,remarkGroup ");
                                    sbCmdText.Append("Where pic.ID=:" + picIdParam);
                                    sbCmdText.Append(" and type.typeName=:typeName ");
                                    sbCmdText.Append(" and remark.remark=:remark ");
                                    sbCmdText.Append(" and remarkGroup.remarkId=remark.id ");
                                    sbCmdText.Append(" and remarkGroup.groupName=:" + groupNameParam + ";");
                                }

                                if (!tempGroup.pic[j].del)
                                {
                                    //更新相片名称
                                    string fileNameParam = "fileName" + i.ToString() + j.ToString();
                                    parameterList.Add(DBHelper.createParameter(fileNameParam, OracleType.VarChar, tempGroup.pic[j].fileName));
                                    sbCmdText.Append("Update Pic Set fileName=:" + fileNameParam
                                        + " Where id=:" + picIdParam
                                        + " and fileName<>:" + fileNameParam + " ;");
                                }
                            }
                        }
                    }
                    sbCmdText.Append(" End;");
                    OracleParameter[] parameters = parameterList.ToArray();
                    //执行语句
                    DBHelper.ExecuteNonQuery(sbCmdText.ToString(), parameters);
                    //return sbCmdText.ToString();
                    return new { success = "OK" };
                }
                else
                {
                    //不存在有效分组，返回错误信息
                    return new { error = "没有有效的相片分组信息。请先上传相片！" };
                }
            }
            catch (Exception ex)
            {
                return new { error = "提交失败", syserror = ex.Message };
            }
        }

        // DELETE api/pictyperemarkgroups
        /// <summary>
        /// 删除相片分组信息(根据指定的证件类型和评语删除相片表和相片分组表记录)
        /// </summary>
        /// <returns></returns>
        public object Delete()
        {
            try
            {
                int remarkId, typeId;
                int.TryParse(HttpContext.Current.Request.QueryString["remarkid"], out remarkId);
                int.TryParse(HttpContext.Current.Request.QueryString["typeid"], out typeId);
                string userId = HttpContext.Current.Request.QueryString["userid"];
                OracleParameter userIdParam = DBHelper.createParameter("userId", OracleType.VarChar, userId);
                OracleParameter remarkIdParam = DBHelper.createParameter("remarkId", OracleType.Int32, remarkId);
                OracleParameter typeIdParam = DBHelper.createParameter("typeId", OracleType.Int32, typeId);                
                StringBuilder sbCmdText = new StringBuilder();
                sbCmdText.Append("Declare Begin ");
                //写操作日志
                sbCmdText.Append("Insert Into user_log(id,userId,oDate,content) ");
                sbCmdText.Append("Select user_log_seq.nextval,:userId,sysDate,'删除： 证件类型：\"'||remark.remark||'\"评语：\"'||type.typeName||'\"' ");
                sbCmdText.Append("From remark,type ");
                sbCmdText.Append("Where remark.id=:remarkId ");
                sbCmdText.Append("and type.id=:typeId; ");
                //删除证件类型评语关系表记录
                sbCmdText.Append("Delete From typeRemark Where typeId=:typeId and remarkId=:remarkId; ");
                //删除相片记录
                sbCmdText.Append("Delete From Pic ");
                sbCmdText.Append("Where Id in (Select PicId From picTypeRemarkGroup Where typeId=:typeId and remarkId=:remarkId); ");
                //删除相片证件类型评语分组表记录
                sbCmdText.Append("Delete From picTypeRemarkGroup Where typeId=:typeId and remarkId=:remarkId; ");
                sbCmdText.Append("End;");

                DBHelper.ExecuteNonQuery(sbCmdText.ToString(), userIdParam, remarkIdParam, typeIdParam);
                return new { success = "OK" };

                //return sbCmdText.ToString();
            }
            catch (Exception ex)
            {
                return new { error = "删除失败", syserror = ex.Message };
            }
        }
    }
}
